﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using Spire.Xls;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Reflection;
using System.Text;
using System.Web;

namespace winXml
{
    public class ExcelHepler
    {
        #region Excel数据转换成DataTable
        /// <summary>
        /// 
        /// </summary>
        /// <param name="ExcelPath"></param>
        /// <returns></returns>
        private static string GetConStr(string ExcelPath)
        {
            string path = ExcelPath;
            if (!File.Exists(path))
                return null;
            string str2 = Path.GetExtension(path).ToLower();
            if ((str2 != ".xls") && (str2 != ".xlsx"))
                return null;
            string str3 = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" + path + "; Extended Properties=Excel 8.0";
            if (str2 == ".xlsx")
                str3 = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source=" + path + "; Extended Properties=Excel 12.0";
            return str3;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ExcelPath"></param>
        /// <returns></returns>
        public static DataTable ExcelDataTable(string ExcelPath)
        {
            return ExcelDataTable(ExcelPath, null);
        }

        /// <summary>
        /// Excel数据转换成DataTable
        /// </summary>
        /// <param name="ExcelPath">Excel文件的绝对路径</param>
        /// <param name="SheetName">工作单</param>
        /// <returns></returns>
        public static DataTable ExcelDataTable(string ExcelPath, string SheetName)
        {
            string conStr = GetConStr(ExcelPath);
            if (string.IsNullOrEmpty(conStr))
                return null;
            OleDbConnection connection = new OleDbConnection(conStr);
            connection.Open();
            if (string.IsNullOrEmpty(SheetName))
                SheetName = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
            else if (!SheetName.Contains("$"))
                SheetName = SheetName + "$";
            OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [" + SheetName + "]", conStr);
            DataSet dataSet = new DataSet();
            adapter.Fill(dataSet, "[" + SheetName + "$]");
            connection.Close();
            return dataSet.Tables[0];
        }
        #endregion

        #region Excel数据转换成DataSet
        /// <summary>
        /// Excel数据转换成DataSet
        /// </summary>
        /// <param name="filepath">Excel文件的绝对路径</param>
        /// <param name="sheetname">工作单</param>
        /// <returns></returns>
        public static DataSet ExcelDataSet(string filepath, string sheetname)
        {
            string strConn;
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "$]", strConn);
            DataSet ds = new DataSet();
            oada.Fill(ds);
            return ds;
        }
        #endregion

        #region 从Excel中获得工作单（SheetName）
        /// <summary>
        /// 从Excel中获得工作单（sheetname）
        /// </summary>
        /// <param name="filepath">Excel文件的绝对路径</param>
        /// <returns></returns>
        public static ArrayList ExcelSheetName(string filepath)
        {
            ArrayList al = new ArrayList();
            string strConn;
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            DataTable sheetNames = conn.GetOleDbSchemaTable
            (System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            conn.Close();
            foreach (DataRow dr in sheetNames.Rows)
            {
                al.Add(dr[2]);
            }
            return al;
        }
        #endregion

        public static void DataTableToExcel(DataTable dt, string path)
        {
            Workbook workbook = new Workbook();

            //Initailize worksheet
            Worksheet sheet = workbook.Worksheets[0];

            sheet.InsertDataTable(dt, true, 1, 1, -1, -1);

            //Sets body style
            CellStyle oddStyle = workbook.Styles.Add("oddStyle");
            oddStyle.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
            oddStyle.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
            oddStyle.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
            oddStyle.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
            oddStyle.KnownColor = ExcelColors.LightGreen1;

            CellStyle evenStyle = workbook.Styles.Add("evenStyle");
            evenStyle.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
            evenStyle.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
            evenStyle.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
            evenStyle.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
            evenStyle.KnownColor = ExcelColors.LightTurquoise;

            foreach (CellRange range in sheet.AllocatedRange.Rows)
            {
                if (range.Row % 2 == 0)
                    range.CellStyleName = evenStyle.Name;
                else
                    range.CellStyleName = oddStyle.Name;
            }

            //Sets header style
            CellStyle styleHeader = sheet.Rows[0].Style;
            styleHeader.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
            styleHeader.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
            styleHeader.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
            styleHeader.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
            styleHeader.VerticalAlignment = VerticalAlignType.Center;
            styleHeader.KnownColor = ExcelColors.Green;
            styleHeader.Font.KnownColor = ExcelColors.White;
            styleHeader.Font.IsBold = true;

            sheet.AllocatedRange.AutoFitColumns();
            sheet.AllocatedRange.AutoFitRows();

            sheet.Rows[0].RowHeight = 20;

            workbook.SaveToFile(path);
        }

        /// <summary>
        /// 生成Excel文件
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="strExcelFileName">文件名</param>
        /// <param name="isWebDownload">如果是web下载，strExcelFileName则仅仅是文件名而非路径名</param>
        public static void GridToExcelByNPOI(DataTable dt, string strExcelFileName, bool isWebDownload = false)
        {
            string TempFilePath = strExcelFileName;
            HSSFWorkbook workbook = new HSSFWorkbook();
            try
            {
                int sheetIndex = 0;
                int dataIndex = 0;
                ICellStyle HeadercellStyle = workbook.CreateCellStyle();
                HeadercellStyle.BorderBottom = BorderStyle.Thin;
                HeadercellStyle.BorderLeft = BorderStyle.Thin;
                HeadercellStyle.BorderRight = BorderStyle.Thin;
                HeadercellStyle.BorderTop = BorderStyle.Thin;
                HeadercellStyle.Alignment = HorizontalAlignment.Center;

                ICellStyle cellStyle = workbook.CreateCellStyle();

                //为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
                cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
                cellStyle.BorderBottom = BorderStyle.Thin;
                cellStyle.BorderLeft = BorderStyle.Thin;
                cellStyle.BorderRight = BorderStyle.Thin;
                cellStyle.BorderTop = BorderStyle.Thin;

                //字体
                IFont headerfont = workbook.CreateFont();
                headerfont.Boldweight = (short)FontBoldWeight.Bold;
                HeadercellStyle.SetFont(headerfont);


                IFont cellfont = workbook.CreateFont();
                cellfont.Boldweight = (short)FontBoldWeight.Normal;
                cellStyle.SetFont(cellfont);
                while (dataIndex < dt.Rows.Count)
                {
                    sheetIndex++;
                    ISheet sheet = workbook.CreateSheet($"Sheet{sheetIndex}");

                    //用column name 作为列名
                    int icolIndex = 0;
                    IRow headerRow = sheet.CreateRow(0);
                    foreach (DataColumn item in dt.Columns)
                    {
                        ICell cell = headerRow.CreateCell(icolIndex);
                        cell.SetCellValue(item.ColumnName);
                        cell.CellStyle = HeadercellStyle;
                        icolIndex++;
                    }


                    //建立内容行
                    int iRowIndex = 1;
                    int iCellIndex = 0;
                    for (int count = 0; dataIndex < dt.Rows.Count; dataIndex++, count++)
                    {
                        if (count >= 65000)
                            break;
                        DataRow Rowitem = dt.Rows[dataIndex];
                        IRow DataRow = sheet.CreateRow(iRowIndex);
                        foreach (DataColumn Colitem in dt.Columns)
                        {

                            ICell cell = DataRow.CreateCell(iCellIndex);
                            cell.SetCellValue(Rowitem[Colitem].ToString());
                            cell.CellStyle = cellStyle;
                            iCellIndex++;
                        }
                        iCellIndex = 0;
                        iRowIndex++;
                    }

                    //自适应列宽度
                    for (int i = 0; i < icolIndex; i++)
                    {
                        sheet.AutoSizeColumn(i);
                    }
                }

                //如果没有传路径,就生成用于web下载的流
                if (isWebDownload)
                {
                    using (MemoryStream ms = new MemoryStream())
                    {
                        workbook.Write(ms);
                        ms.Flush();
                        ms.Position = 0;

                        byte[] bytes = ms.GetBuffer();
                        string filename = strExcelFileName + ".xls";
                    }
                }
                else
                {
                    if (System.IO.File.Exists(TempFilePath))
                        System.IO.File.Delete(TempFilePath);

                    using (FileStream file = new FileStream(TempFilePath, FileMode.Create))
                    {
                        workbook.Write(file);  //创建test.xls文件。
                        file.Close();
                    }
                        //写Excel
                       //FileStream file = new FileStream(TempFilePath, FileMode.Create);
                    //workbook.Write(file);
                    //file.Flush();
                    //file.Close();
                }





            }
            catch (Exception ex)
            {
                throw;
            }
            finally { workbook = null; }

        }

        public static DataTable ListToDataTable<T>(List<T> entitys)
        {

            //检查实体集合不能为空
            if (entitys == null || entitys.Count < 1)
            {
                return new DataTable();
            }

            //取出第一个实体的所有Propertie
            Type entityType = entitys[0].GetType();
            PropertyInfo[] entityProperties = entityType.GetProperties();

            //生成DataTable的structure
            //生产代码中，应将生成的DataTable结构Cache起来，此处略
            DataTable dt = new DataTable("dt");
            for (int i = 0; i < entityProperties.Length; i++)
            {
                //dt.Columns.Add(entityProperties[i].Name, entityProperties[i].PropertyType);
                dt.Columns.Add(entityProperties[i].Name);
            }

            //将所有entity添加到DataTable中
            foreach (object entity in entitys)
            {
                //检查所有的的实体都为同一类型
                if (entity.GetType() != entityType)
                {
                    throw new Exception("要转换的集合元素类型不一致");
                }
                object[] entityValues = new object[entityProperties.Length];
                for (int i = 0; i < entityProperties.Length; i++)
                {
                    entityValues[i] = entityProperties[i].GetValue(entity, null);

                }
                dt.Rows.Add(entityValues);
            }
            return dt;
        }
    }
}
